package com.example.homework.controller;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class Business {
    private final String dbUrl =
            "jdbc:sqlserver://localhost:1433;database=homework;trustServerCertificate=true";
    private final String userName = "sa";
    private final String userPwd = "bjdjr0544";
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private String sql;

    public Map<String, Object> businessAddfood(int bid, String food, String price,
                                               byte[] img, String desc, int stock,
                                               String type, int rec, String status) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Food where Bid = " + bid + " and Fname = '" + food
                    + "' and Fdel = 0";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                state = 1;
            } else {
                sql = "select Fmax from Id_max where temp = 0";
                rs = st.executeQuery(sql);
                rs.next();
                int myId = rs.getInt("Fmax") + 1;
                sql = "update Id_max set Fmax = " + myId + " where temp = 0";
                st.executeUpdate(sql);
                PreparedStatement prest;
                int myStock = stock;
                if (myStock > 100) {
                    myStock = 100;
                } else if (myStock < 0) {
                    myStock = 0;
                }
                prest = con.prepareStatement("insert into Food values ("
                        + myId + ", " + bid + ", '" + food + "', " + price + ", ?, '"
                        + desc + "', " + myStock + ", '" + type + "', " + rec + ", '"
                        + status + "', N'未审核', 0)");
                Blob blob = con.createBlob();
                blob.setBytes(1, img);
                prest.setBlob(1, blob);
                prest.executeUpdate();
                prest = con.prepareStatement("insert into Record(Rtype, Rtime, Rid, Fid, Fname, " +
                        "Fprice, Fimg, Fdesc, Fstock, Ftype, Frec, Fstatus) values (3, getdate(), "
                        + bid + ", " + myId +  ", '" + food + "', " + price + ", ?, '" + desc
                        + "', " + myStock + ", '" + type + "', " + rec + ", '" + status + "')");
                prest.setBlob(1, blob);
                prest.executeUpdate();
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public Map<String, Object> businessChangefood(int bid, int id, String food, String price,
                                                  String desc, int stock, String type,
                                                  int rec, String status) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Food where Fid = " + id + " and Bid = " + bid +
                    " and Fpass = N'已审核' and Fdel = 0";
            rs = st.executeQuery(sql);
            if (!rs.next()) {
                state = 1;
            } else {
                int myStock = stock;
                if (myStock > 100) {
                    myStock = 100;
                } else if (myStock < 0) {
                    myStock = 0;
                }
                sql = "select * from Food where Fid != " + id + " and Bid = " + bid +
                        " and Fdel = 0 and Fname = '" + food + "'";
                rs = st.executeQuery(sql);
                if (rs.next()) {
                    state = 2;
                } else {
                    sql = "update Food set Fname = '" + food + "', Fprice = " + price +
                            ", Fdesc = '" + desc + "', Fstock = " + myStock + ", Ftype = '" + type
                            + "', Frec = " + rec + ", Fstatus = '" + status + "' where Fid = " + id;
                    st.executeUpdate(sql);
                    sql = "insert into Record(Rtype, Rtime, Rid, Fid, Fname, " +
                            "Fprice, Fdesc, Fstock, Ftype, Frec, Fstatus) values (4, getdate(), "
                            + bid + ", " + id +  ", '" + food + "', " + price + ", '" + desc
                            + "', " + myStock + ", '" + type + "', " + rec + ", '" + status + "')";
                    st.executeUpdate(sql);
                }
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public Map<String, Object> businessDelfood(int bid, int id) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Food where Fid = " + id + " and Bid = " + bid +
                    " and Fpass = N'已审核' and Fdel = 0";
            rs = st.executeQuery(sql);
            if (!rs.next()) {
                state = 1;
            } else {
                sql = "update Food set Fdel = 1 where Fid = " + id;
                st.executeUpdate(sql);
                sql = "insert into Record(Rtype, Rtime, Rid, Fid) values (5, getdate(), "
                        + bid + ", " + id + ")";
                st.executeUpdate(sql);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public Map<String, Object> businessOnefood(int bid, int id) {
        Map<String, Object> map = makeFoodMap(0);
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Food where Fid = " + id + " and Bid = " + bid +
                    " and Fpass = N'已审核' and Fdel = 0";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                map = makeFoodMap(1);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return map;
    }

    public Map<String, Object> makeFoodMap(int choice) { // 0异常初始化，1根据rs
        Map<String, Object> map = new HashMap<>();
        if (choice == 0) {
            map.put("state", 1);
            map.put("id", 0);
            map.put("shop", "None");
            map.put("food", "None");
            map.put("price", "0");
            map.put("img", new byte[0]);
            map.put("desc", "None");
            map.put("stock", 0);
            map.put("type", "None");
            map.put("rec", 0);
            map.put("status", "下架");
        } else {
            try {
                map.put("state", 0);
                map.put("id", rs.getInt("Fid"));
                map.put("food", rs.getString("Fname"));
                map.put("price", String.valueOf(rs.getFloat("Fprice")));
                map.put("img", rs.getBytes("Fimg"));
                map.put("desc", rs.getString("Fdesc"));
                map.put("stock", rs.getInt("Fstock"));
                map.put("type", rs.getString("Ftype"));
                map.put("rec", rs.getInt("Frec"));
                map.put("status", rs.getString("Fstatus"));
                String temp = "select * from Buser where Bid = " + rs.getInt("Bid");
                ResultSet tempRs = st.executeQuery(temp);
                if (tempRs.next()) {
                    map.put("shop", tempRs.getString("Bname"));
                } else {
                    map.put("shop", "None");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    public ArrayList<Map<String, Object>> businessAnyfood(
            int bid, String id, String food, String type) {
        ArrayList<Map<String, Object>> ans = new ArrayList<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            if (!id.equals("null")) {
                sql = "select * from Food where Fid = " + Integer.parseInt(id) + " and Bid = "
                        + bid + " and Fpass = N'已审核' and Fdel = 0";
            } else if (!food.equals("null")) {
                sql = "select * from Food where Fname = '" + food + "' and Bid = "
                        + bid + " and Fpass = N'已审核' and Fdel = 0";
            } else {
                sql = "select * from Food where Ftype = '" + type + "' and Bid = "
                        + bid + " and Fpass = N'已审核' and Fdel = 0";
            }
            rs = st.executeQuery(sql);
            while (rs.next()) {
                ans.add(makeFoodMap(1));
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }

    public ArrayList<Map<String, Object>> businessAllfood(int bid) {
        ArrayList<Map<String, Object>> ans = new ArrayList<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Food where Bid = " + bid + " and Fpass = N'已审核' and Fdel = 0";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                ans.add(makeFoodMap(1));
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }

    public ArrayList<Map<String, Object>> businessAllorder(int bid) {
        ArrayList<Map<String, Object>> ans = new ArrayList<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Order_shop where Bid = " + bid +
                    " and Ostatus != N'未通过' and Odel = 0";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                ans.add(makeOrderMap(1));
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }

    public Map<String, Object> makeOrderMap(int choice) { // 0异常初始化，1根据rs
        Map<String, Object> map = new HashMap<>();
        if (choice == 0) {
            map.put("state", 1);
            map.put("id", 0);
            map.put("phone", "0");
            map.put("time", "2023-1-1 0:0:0");
            map.put("shop", "None");
            map.put("cust", "None");
            map.put("value", "0");
            map.put("status", "未接单");
            map.put("foods", new ArrayList<>());
            map.put("tip", "None");
        } else {
            try {
                map.put("state", 0);
                map.put("id", rs.getInt("Oid"));
                map.put("phone", rs.getString("Ctele"));
                map.put("time", rs.getDate("Otime") + " " + rs.getTime("Otime"));
                map.put("value", String.valueOf(rs.getFloat("Oval")));
                map.put("status", rs.getString("Ostatus"));
                String temp = "select * from Buser where Bid = " + rs.getInt("Bid");
                ResultSet tempRs = st.executeQuery(temp);
                if (tempRs.next()) {
                    map.put("shop", tempRs.getString("Bname"));
                } else {
                    map.put("shop", "None");
                }
                temp = "select * from Cuser where Cid = " + rs.getInt("Cid");
                tempRs = st.executeQuery(temp);
                if (tempRs.next()) {
                    map.put("cust", tempRs.getString("Cname"));
                } else {
                    map.put("cust", "None");
                }
                temp = "select * from Order_food where Oid = " + rs.getInt("Oid");
                tempRs = st.executeQuery(temp);
                ArrayList<Map<String, Object>> foods = new ArrayList<>();
                while (tempRs.next()) {
                    Map<String, Object> subMap = new HashMap<>();
                    subMap.put("food", tempRs.getString("Fname"));
                    subMap.put("num", tempRs.getInt("Fnum"));
                    foods.add(subMap);
                }
                map.put("foods", foods);
                map.put("tip", rs.getString("Otip"));
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    public ArrayList<Map<String, Object>> businessAnyorder(int bid, String id, String cust) {
        ArrayList<Map<String, Object>> ans = new ArrayList<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            if (!id.equals("null")) {
                sql = "select * from Order_shop where Oid = " + Integer.parseInt(id) + " and Bid = "
                        + bid + " and Ostatus != N'未通过' and Odel = 0";
            } else {
                int cid = 0;
                sql = "select * from Cuser where Cname = " + cust;
                rs = st.executeQuery(sql);
                if (rs.next()) {
                    cid = rs.getInt("Cid");
                }
                sql = "select * from Order_shop where Cid = " + cid + " and Bid = "
                        + bid + " and Ostatus != N'未通过' and Odel = 0";
            }
            rs = st.executeQuery(sql);
            while (rs.next()) {
                ans.add(makeOrderMap(1));
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }

    public Map<String, Object> businessAcorder(int bid, int id) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Order_shop where Bid = " + bid + " and Oid = " + id +
                    " and Ostatus = N'未接单' and Odel = 0";
            rs = st.executeQuery(sql);
            if (!rs.next()) {
                state = 1;
            } else {
                sql = "update Order_shop set Ostatus = N'已接单' where Oid = " + id;
                st.executeUpdate(sql);
                sql = "insert into Record(Rtype, Rtime, Rid, Oid, Ostatus) values (6, getdate(), "
                        + bid + ", " + id + ", N'已接单')";
                st.executeUpdate(sql);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public Map<String, Object> businessOneorder(int bid, int id) {
        Map<String, Object> map = new HashMap<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Order_shop where Bid = " + bid + " and Oid = " + id +
                    " and Ostatus != N'未通过' and Odel = 0";
            rs = st.executeQuery(sql);
            if (!rs.next()) {
                map = makeOrderMap(0);
            } else {
                map = makeOrderMap(1);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return map;
    }

    public Map<String, Object> businessDelorder(int bid, int id) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Order_shop where Bid = " + bid + " and Oid = " + id +
                    " and Ostatus != N'未通过' and Odel = 0";
            rs = st.executeQuery(sql);
            if (!rs.next()) {
                state = 1;
            } else {
                sql = "update Order_shop set Odel = 1 where Oid = " + id;
                st.executeUpdate(sql);
                sql = "insert into Record(Rtype, Rtime, Rid, Oid) values (7, getdate(), "
                        + bid + ", " + id + ")";
                st.executeUpdate(sql);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public ArrayList<Map<String, Object>> businessTimeorder(int bid, String begin, String end) {
        ArrayList<Map<String, Object>> ans = new ArrayList<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Order_shop where Bid = " + bid + " and Otime >= '" + begin +
                    "' and Otime <= '" + end + "' and Ostatus != N'未通过' and Odel = 0";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("order", String.valueOf(rs.getInt("Oid")));
                map.put("time", rs.getDate("Otime") + " " + rs.getTime("Otime"));
                map.put("value", rs.getString("Oval"));
                ans.add(map);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }
}
